create database PhotoBox
go
use PhotoBox
go
create table UserAccount(
	userid int primary key identity,
	username nvarchar(200) not null unique,
	password nvarchar(200) not null,
	fullname nvarchar(200) not null,
	dateofbirth datetime not null,
	email nvarchar(200) not null,
	phonenumber nvarchar(200) not null,
	image nvarchar(200),
)
go

create table Album(
	albumid int primary key identity,
	albumname nvarchar(200) not null unique,
	description nvarchar(500),
	datecreate datetime,
	username nvarchar(200) foreign key references UserAccount(username),
)
go

create table [Image](
	id int primary key identity,
	imagename nvarchar(200) not null unique,
	dayshooting datetime,
	username nvarchar(200) foreign key references UserAccount(username),
	albumname nvarchar(200) foreign key references Album(albumname),
	turnlike nvarchar(200),
	introduction nvarchar(500)
)
go

create proc createAccount
@username nvarchar(200),
@password nvarchar(200),
@fullname nvarchar(200),
@dateofbirth date,
@email nvarchar(200),
@phonenumber nvarchar(200),
@image nvarchar(200)
as
	insert into UserAccount values(@username, @password, @fullname, @dateofbirth, @email, @phonenumber, @image)
go
create proc createAlbum
@albumname nvarchar(200),
@description nvarchar(500),
@datecreate date,
@username nvarchar(200)
as
	insert into Album values(@albumname, @description, @datecreate, @username)
go	

create proc showAlbum
as
	select * from Album
go	

create proc [login]
	@username varchar(200),
	@password varchar(200)
as
	select userid, username, [password] from UserAccount where username = @username and [password]=@password
go	

insert into UserAccount values('admin', 'e10adc3949ba59abbe56e057f20f883e', 'Hieplm', '10/10/1992', 'hieplmgc00374@gmail.com', 0987654321, '487969_643267412392757_185875677_n.jpg')
insert into UserAccount values('tungmt', 'e10adc3949ba59abbe56e057f20f883e', 'tungmt', '11/11/1992', 'tungmt@gmail.com', 0123456789, 'efddfdfb5677_n.jpg')
go

select * from UserAccount
go

create proc insertImage
@imagename nvarchar(200),
@dayshooting datetime,
@username nvarchar(200),
@albumname nvarchar(200),
@turnlike nvarchar(200),
@introduction nvarchar(500)
as
	insert into [Image] values(@imagename, @dayshooting, @username, @albumname, @turnlike, @introduction)
go	


select * from [Image]
go

select * from Album
go